const { pool } = require('../config/db');

// 创建订单地址表
exports.createOrderAddressTable = async () => {
  const query = `
    CREATE TABLE IF NOT EXISTS order_addresses (
      id INT AUTO_INCREMENT PRIMARY KEY,
      order_id INT NOT NULL,
      consignee_name VARCHAR(50) NOT NULL,
      phone_number VARCHAR(20) NOT NULL,
      province VARCHAR(50),
      city VARCHAR(50),
      district VARCHAR(50),
      detail_address TEXT NOT NULL,
      remarks TEXT,
      delivery_time VARCHAR(50),
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      -- 移除外键约束，允许用户直接提交地址信息
    )
  `;
  
  try {
    await pool.execute(query);
    console.log('Order address table created successfully!');
  } catch (error) {
    console.error('Error creating order address table:', error);
  }
};

// 创建或更新订单地址
exports.saveOrderAddress = async (addressData) => {
  // 防御性编程，确保必要参数都有值
  const safeAddressData = {
    order_id: addressData.order_id || 0,
    consignee_name: addressData.consignee_name || '',
    phone_number: addressData.phone_number || '',
    province: addressData.province || '',
    city: addressData.city || '',
    district: addressData.district || '',
    detail_address: addressData.detail_address || '',
    remarks: addressData.remarks || '',
    delivery_time: addressData.delivery_time || ''
  };
  
  const { order_id, consignee_name, phone_number, province, city, district, detail_address, remarks, delivery_time } = safeAddressData;
  
  // 对字符串值进行转义，防止SQL语法错误
  const escapeString = (str) => {
    if (typeof str !== 'string') return str;
    // 替换单引号为两个单引号（SQL转义标准方式）
    return str.replace(/'/g, "''");
  };
  
  const escapedConsigneeName = escapeString(consignee_name);
  const escapedPhoneNumber = escapeString(phone_number);
  const escapedProvince = escapeString(province);
  const escapedCity = escapeString(city);
  const escapedDistrict = escapeString(district);
  const escapedDetailAddress = escapeString(detail_address);
  const escapedRemarks = escapeString(remarks);
  const escapedDeliveryTime = escapeString(delivery_time);
  
  // 先检查是否已存在该订单的地址
  const existingAddress = await this.getAddressByOrderId(order_id);
  
  if (existingAddress) {
    // 如果存在则更新 - 使用字符串拼接方式
    const updateQuery = `
      UPDATE order_addresses 
      SET consignee_name = '${escapedConsigneeName}', phone_number = '${escapedPhoneNumber}', province = '${escapedProvince}', city = '${escapedCity}', district = '${escapedDistrict}', 
          detail_address = '${escapedDetailAddress}', remarks = '${escapedRemarks}', delivery_time = '${escapedDeliveryTime}' 
      WHERE order_id = ${order_id}
    `;
    
    try {
      await pool.execute(updateQuery);
      return this.getAddressByOrderId(order_id);
    } catch (error) {
      console.error('更新订单地址时发生SQL错误:', error);
      throw error;
    }
  } else {
    // 如果不存在则创建 - 使用字符串拼接方式
    const insertQuery = `
      INSERT INTO order_addresses 
      (order_id, consignee_name, phone_number, province, city, district, detail_address, remarks, delivery_time) 
      VALUES (${order_id}, '${escapedConsigneeName}', '${escapedPhoneNumber}', '${escapedProvince}', '${escapedCity}', '${escapedDistrict}', '${escapedDetailAddress}', '${escapedRemarks}', '${escapedDeliveryTime}')
    `;
    
    try {
      await pool.execute(insertQuery);
      return this.getAddressByOrderId(order_id);
    } catch (error) {
      console.error('创建订单地址时发生SQL错误:', error);
      throw error;
    }
  }
};

// 根据订单ID获取地址信息
exports.getAddressByOrderId = async (orderId) => {
  const query = `SELECT * FROM order_addresses WHERE order_id = ${orderId}`;
  const [rows] = await pool.execute(query);
  return rows[0] || null;
};

// 根据ID获取地址信息
exports.getAddressById = async (id) => {
  const query = `SELECT * FROM order_addresses WHERE id = ${id}`;
  const [rows] = await pool.execute(query);
  return rows[0] || null;
};

// 删除订单地址
exports.deleteOrderAddress = async (orderId) => {
  const query = `DELETE FROM order_addresses WHERE order_id = ${orderId}`;
  try {
    await pool.execute(query);
    return true;
  } catch (error) {
    console.error('删除订单地址时发生SQL错误:', error);
    throw error;
  }
};

// 灵活查询订单地址
exports.searchOrderAddresses = async (searchConditions, page = 1, pageSize = 10) => {
  // 确保参数是有效的数字类型
  const pageNum = Number.isInteger(Number(page)) ? Number(page) : 1;
  const size = Number.isInteger(Number(pageSize)) ? Number(pageSize) : 10;
  const offset = (pageNum - 1) * size;
  let query = 'SELECT * FROM order_addresses';
  let hasWhere = false;
  let whereClause = '';
  
  // 构建WHERE条件
  if (searchConditions) {
    // 订单ID精确匹配
    if (searchConditions.order_id) {
      whereClause += hasWhere ? ` AND order_id = ${searchConditions.order_id}` : ` WHERE order_id = ${searchConditions.order_id}`;
      hasWhere = true;
    }
    
    // 收货人姓名模糊匹配
    if (searchConditions.consignee_name) {
      const consigneeName = `%${searchConditions.consignee_name}%`;
      whereClause += hasWhere ? ` AND consignee_name LIKE '${consigneeName}'` : ` WHERE consignee_name LIKE '${consigneeName}'`;
      hasWhere = true;
    }
    
    // 手机号模糊匹配
    if (searchConditions.phone_number) {
      const phoneNumber = `%${searchConditions.phone_number}%`;
      whereClause += hasWhere ? ` AND phone_number LIKE '${phoneNumber}'` : ` WHERE phone_number LIKE '${phoneNumber}'`;
      hasWhere = true;
    }
    
    // 省份精确匹配
    if (searchConditions.province) {
      whereClause += hasWhere ? ` AND province = '${searchConditions.province}'` : ` WHERE province = '${searchConditions.province}'`;
      hasWhere = true;
    }
    
    // 城市精确匹配
    if (searchConditions.city) {
      whereClause += hasWhere ? ` AND city = '${searchConditions.city}'` : ` WHERE city = '${searchConditions.city}'`;
      hasWhere = true;
    }
    
    // 区域精确匹配
    if (searchConditions.district) {
      whereClause += hasWhere ? ` AND district = '${searchConditions.district}'` : ` WHERE district = '${searchConditions.district}'`;
      hasWhere = true;
    }
    
    // 详细地址模糊匹配
    if (searchConditions.detail_address) {
      const detailAddress = `%${searchConditions.detail_address}%`;
      whereClause += hasWhere ? ` AND detail_address LIKE '${detailAddress}'` : ` WHERE detail_address LIKE '${detailAddress}'`;
      hasWhere = true;
    }
  }
  
  // 添加WHERE条件
  query += whereClause;
  
  // 添加排序和分页 - 使用字符串拼接方式
  query += ` ORDER BY created_at DESC LIMIT ${size} OFFSET ${offset}`;
  
  try {
    const [rows] = await pool.execute(query);
    
    // 获取总记录数 - 使用字符串拼接方式
    let countQuery = 'SELECT COUNT(*) as total FROM order_addresses';
    countQuery += whereClause;
    const [countRows] = await pool.execute(countQuery);
    
    return {
      data: rows,
      total: countRows[0].total,
      page: pageNum,
      pageSize: size
    };
  } catch (error) {
    console.error('搜索订单地址时发生SQL错误:', error);
    throw error;
  }
};

// 获取全部订单地址
exports.getAllOrderAddresses = async (page = 1, pageSize = 10, searchQuery = '') => {
  // 确保参数是有效的数字类型
  const pageNum = Number.isInteger(Number(page)) ? Number(page) : 1;
  const size = Number.isInteger(Number(pageSize)) ? Number(pageSize) : 10;
  const offset = (pageNum - 1) * size;
  let query = 'SELECT * FROM order_addresses';
  
  // 如果有搜索条件，则添加WHERE子句
  if (searchQuery) {
    const searchParam = `%${searchQuery}%`;
    query += ` WHERE order_id LIKE '${searchParam}' OR consignee_name LIKE '${searchParam}' OR phone_number LIKE '${searchParam}'`;
  }
  
  // 添加排序和分页 - 使用字符串拼接方式
  query += ` ORDER BY created_at DESC LIMIT ${size} OFFSET ${offset}`;
  
  try {
    const [rows] = await pool.execute(query);
    
    // 获取总记录数 - 使用字符串拼接方式
    let countQuery = 'SELECT COUNT(*) as total FROM order_addresses';
    if (searchQuery) {
      const searchParam = `%${searchQuery}%`;
      countQuery += ` WHERE order_id LIKE '${searchParam}' OR consignee_name LIKE '${searchParam}' OR phone_number LIKE '${searchParam}'`;
    }
    const [countRows] = await pool.execute(countQuery);
    
    return {
      data: rows,
      total: countRows[0].total,
      page: pageNum,
      pageSize: size
    };
  } catch (error) {
    console.error('获取全部订单地址时发生SQL错误:', error);
    throw error;
  }
};